package service;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.HashMap;

@WebServlet("/topStudents")
public class Most_Good_Stu {
    private static final long serialVersionUID = 1L;

    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        resp.setContentType("application/json");
        resp.setCharacterEncoding("UTF-8");

        String jdbcUrl = "jdbc:mysql://192.168.244.11:3306/stats?serverTimezone=Asia/Shanghai";
        String dbUser = "root";
        String dbPassword = "123456";

        try {
            // 显式加载驱动程序
            Class.forName("com.mysql.cj.jdbc.Driver");

            try (Connection conn = DriverManager.getConnection(jdbcUrl, dbUser, dbPassword)) {
                // 查询绩点最高的3名学生
                String query = "SELECT student_id, predicted_gpa FROM gpa_recommendations " +
                        "ORDER BY predicted_gpa DESC LIMIT 3";

                try (PreparedStatement stmt = conn.prepareStatement(query);
                     ResultSet rs = stmt.executeQuery()) {

                    List<Map<String, Object>> topStudents = new ArrayList<>();
                    while (rs.next()) {
                        Map<String, Object> student = new HashMap<>();
                        student.put("student_id", rs.getString("student_id"));
                        student.put("gpa", rs.getDouble("predicted_gpa"));
                        topStudents.add(student);
                    }

                    // 构建JSON响应
                    try (PrintWriter out = resp.getWriter()) {
                        out.write("{\"topStudents\":");
                        out.write(topStudents.toString());
                        out.write("}");
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            resp.sendError(HttpServletResponse.SC_INTERNAL_SERVER_ERROR,
                    "Failed to fetch top students from MySQL: " + e.getMessage());
        }
    }
}
